﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace DataAccess
{
    /// <summary>
    /// ALTER DATABASE MyDatabase
    ///    SET ALLOW_SNAPSHOT_ISOLATION ON
    /// </summary>
    public class DbHelper
    {
        private Database db = null;

        private static object slocker = new object();
        private DbHelper() { 
             string connectionString = ConfigurationManager.ConnectionStrings["first"].ConnectionString;
             db = new SqlDatabase(connectionString);
        }

        static DbHelper instance = null;
        public static DbHelper Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (slocker)
                    {
                        if (instance == null)
                        {
                            instance = new DbHelper();
                        }
                    }
                }

                return instance;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DbConnection CreateConnection()
        {
            DbConnection connection =  db.CreateConnection();
            connection.Open();

            return connection;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="pageNum"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public DataSet GetFutureWebinars(DbTransaction transaction, int pageNum, int pageSize)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_GetScheduledWebinars"))
            {
                db.AddInParameter(cmd, "@CurrentTime", DbType.DateTime, DateTime.Now.ToUniversalTime());
                db.AddInParameter(cmd, "@PageNum", DbType.Int32, pageNum);
                db.AddInParameter(cmd, "@PageSize", DbType.Int32, pageSize);

                if (transaction == null)
                {
                    return db.ExecuteDataSet(cmd);
                }
                else
                {
                    return db.ExecuteDataSet(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="pageNum"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public DataSet GetExistingWebinars(DbTransaction transaction, int pageNum, int pageSize)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_GetExistingWebinars"))
            {
                db.AddInParameter(cmd, "@CurrentTime", DbType.DateTime, DateTime.Now.ToUniversalTime());
                db.AddInParameter(cmd, "@PageNum", DbType.Int32, pageNum);
                db.AddInParameter(cmd, "@PageSize", DbType.Int32, pageSize);

                if (transaction == null)
                {
                    return db.ExecuteDataSet(cmd);
                }
                else
                {
                    return db.ExecuteDataSet(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataSet GetScheduledWebinars(DbTransaction transaction, int pageNum, int pageSize)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_GetScheduledWebinars"))
            {
                db.AddInParameter(cmd, "@CurrentTime", DbType.DateTime, DateTime.Now.ToUniversalTime());
                db.AddInParameter(cmd, "@PageNum", DbType.Int32, pageNum);
                db.AddInParameter(cmd, "@PageSize", DbType.Int32, pageSize);

                if (transaction == null)
                {
                    return db.ExecuteDataSet(cmd);
                }
                else
                {
                    return db.ExecuteDataSet(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="site"></param>
        /// <returns></returns>
        public DateTime? GetLastPollingTime(DbTransaction transaction, string site)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_GetLastPollingTime"))
            {
                db.AddInParameter(cmd, "@Site", DbType.String, site);

                if (transaction == null)
                {
                    return (DateTime?)db.ExecuteScalar(cmd);
                }
                else
                {
                    return (DateTime?)db.ExecuteScalar(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="uid"></param>
        /// <param name="name"></param>
        /// <param name="scheduled"></param>
        /// <param name="uri"></param>
        /// <returns></returns>
        public DataSet AddWebinar(DbTransaction transaction, string uid, string name, DateTime scheduled, string uri, string description)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_AddWebinar"))
            {
                db.AddInParameter(cmd, "@Uid", DbType.String, uid);
                db.AddInParameter(cmd, "@Name", DbType.String, name);
                db.AddInParameter(cmd, "@Scheduled", DbType.DateTime, scheduled.ToUniversalTime());
                db.AddInParameter(cmd, "@Uri", DbType.String, uri);
                db.AddInParameter(cmd, "@Description", DbType.String, description);
                

                if (transaction == null)
                {
                    return  db.ExecuteDataSet(cmd);
                }
                else
                {
                    return db.ExecuteDataSet(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="webinarId"></param>
        /// <param name="userId"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public DataSet AddQuestion(DbTransaction transaction, int webinarId, int userId, string content)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_AddQuestion"))
            {
                db.AddInParameter(cmd, "@WebinarId", DbType.Int32, webinarId);
                db.AddInParameter(cmd, "@UserId", DbType.Int32, userId);
                db.AddInParameter(cmd, "@CreationDate", DbType.DateTime, DateTime.Now.ToUniversalTime());
                db.AddInParameter(cmd, "@Content", DbType.String, content);

                if (transaction == null)
                {
                    return db.ExecuteDataSet(cmd);
                }
                else
                {
                    return db.ExecuteDataSet(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="questionId"></param>
        /// <param name="userId"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public int AddComment(DbTransaction transaction, int questionId, int userId, string content)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_AddComment"))
            {
                db.AddInParameter(cmd, "@QuestionId", DbType.Int32, questionId);
                db.AddInParameter(cmd, "@UserId", DbType.Int32, userId);
                db.AddInParameter(cmd, "@CreationDate", DbType.DateTime, DateTime.Now.ToUniversalTime());
                db.AddInParameter(cmd, "@Content", DbType.String, content);

                if (transaction == null)
                {
                    return (int)db.ExecuteScalar(cmd);
                }
                else
                {
                    return (int)db.ExecuteScalar(cmd, transaction);
                }
            }
        }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="transaction"></param>
       /// <param name="questionId"></param>
       /// <param name="userId"></param>
       /// <param name="thumbsUp"></param>
       /// <returns></returns>
        public bool Vote(DbTransaction transaction, int questionId, int userId, bool thumbsUp)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_AddVote"))
            {
                db.AddInParameter(cmd, "@QuestionId", DbType.Int32, questionId);
                db.AddInParameter(cmd, "@UserId", DbType.Int32, userId);
                db.AddInParameter(cmd, "@ThumbsUp", DbType.Boolean, thumbsUp);
                db.AddInParameter(cmd, "@CreationDate", DbType.DateTime, DateTime.Now.ToUniversalTime());

                if (transaction == null)
                {
                    return (bool)db.ExecuteScalar(cmd);
                }
                else
                {
                    return (bool)db.ExecuteScalar(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="questionId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public bool MarkAsAnswered(DbTransaction transaction, int questionId, int userId)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_MarkAsAnswered"))
            {
                db.AddInParameter(cmd, "@QuestionId", DbType.Int32, questionId);
                db.AddInParameter(cmd, "@UserId", DbType.Int32, userId);
                db.AddInParameter(cmd, "@ClosureDate", DbType.DateTime, DateTime.Now.ToUniversalTime());

                if (transaction == null)
                {
                    return (bool)db.ExecuteScalar(cmd);
                }
                else
                {
                    return (bool)db.ExecuteScalar(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="webinarId"></param>
        /// <returns></returns>
        public DataSet GetQuestions(DbTransaction transaction, int webinarId)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_GetQuestions"))
            {
                db.AddInParameter(cmd, "@WebinarId", DbType.Int32, webinarId);

                if (transaction == null)
                {
                    return db.ExecuteDataSet(cmd);
                }
                else
                {
                    return db.ExecuteDataSet(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="questionId"></param>
        /// <returns></returns>
        public DataSet GetComments(DbTransaction transaction, int questionId)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_GetComments"))
            {
                db.AddInParameter(cmd, "@QuestionId", DbType.Int32, questionId);

                if (transaction == null)
                {
                    return db.ExecuteDataSet(cmd);
                }
                else
                {
                    return db.ExecuteDataSet(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="site"></param>
        /// <param name="lastPollingTime"></param>
        public void SetLastPolling(DbTransaction transaction,  string site, DateTime lastPollingTime)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_SetLastPollingTime"))
            {
                db.AddInParameter(cmd, "@Site", DbType.String, site);
                db.AddInParameter(cmd, "@LastPollingTime", DbType.DateTime, lastPollingTime);

                if (transaction == null)
                {
                    db.ExecuteNonQuery(cmd);
                }
                else
                {
                    db.ExecuteNonQuery(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="un"></param>
        /// <param name="p1"></param>
        /// <param name="isAdmin"></param>
        /// <returns></returns>
        public int RegisterUser(DbTransaction transaction, string un, string p1, bool isAdmin)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_RegisterUser"))
            {
                db.AddInParameter(cmd, "@N", DbType.String, un);
                db.AddInParameter(cmd, "@P", DbType.String, p1);
                db.AddInParameter(cmd, "@IsAdmin", DbType.Boolean, isAdmin);
                db.AddInParameter(cmd, "@RegistrationDate", DbType.DateTime, DateTime.Now.ToUniversalTime());
                

                if (transaction == null)
                {
                    return (int)db.ExecuteScalar(cmd);
                }
                else
                {
                    return (int)db.ExecuteScalar(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="n"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public DataSet Login(DbTransaction transaction, string n, string p)
        {
            using (DbCommand cmd = db.GetStoredProcCommand("sp_GetUserInfo"))
            {
                db.AddInParameter(cmd, "@N", DbType.String, n);
                db.AddInParameter(cmd, "@P", DbType.String, p);

                if (transaction == null)
                {
                    return db.ExecuteDataSet(cmd);
                }
                else
                {
                    return db.ExecuteDataSet(cmd, transaction);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public DataSet GetUsers(DbTransaction transaction, string userName)
        {
            using (SqlCommand cmd = db.GetSqlStringCommand("SELECT  [ID], [Name], IsAdmin, [RegistrationDate] FROM [dbo].[Users] WHERE Name = @UserName") as SqlCommand)
            {
                cmd.Parameters.AddWithValue("@UserName", userName);
                if (transaction == null)
                {
                    return db.ExecuteDataSet(cmd);
                }
                else
                {
                    return db.ExecuteDataSet(cmd, transaction);
                }
            }
        }
    }
}
